﻿using DXApplicationTangche.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DXApplicationTangche.UC.报表.service
{
    public class 报表Service
    {
        public static DataTable getdata()
        {
            DataTable dt = SQLpostgres.GetDataTable("select * from t_barcode_wip_instance limit 20;");
            return dt;
        }

        public static DataTable get每日工序汇总()
        {
            String sql = "select\n" +
                        "datestr ,\n" +
                        "caijian ,\n" +
                        "semicheck ,\n" +
                        "finishcheck ,\n" +
                        "dingsuo ,\n" +
                        "stockinquantity\n" +
                        "from\n" +
                        "t_wip_summary\n" +
                        "order by\n" +
                        "datestr desc;\n"; ;
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }

        public static DataTable get裁剪()
        {
            String sql = "-- 1.每日裁剪：\n" +
                " select" +
                 " t_project_instance.order_no as order_no, --订单号\n" +
                 " t_project_instance.order_date as order_date, --订单日期\n" +
                 " t_project_instance.complete_date as complete_date, --裁剪完成时间\n" +
                 " t_project_instance.style_name as style_name, --款式名称\n" +
                 " t_project_instance.fabric_no as fabric_no, --面料号\n" +
                 " SPLIT_PART(t_project_instance.\"size\", '-', 2) as size, --尺码\n" +
                 " t_project_instance.quantity as quantity, --裁剪数量\n" +
                 " t_order_report_p.shopname as shopname--订单类型\n" +
                " from" +
                " t_project_instance" +
                " left join t_order_report_p on" +
                " t_project_instance.order_id = t_order_report_p.orderid" +
                " where" +
                " t_project_instance.procedure_name = '00.0003.0001'" +
                " and t_project_instance.complete_date >= current_date - 1" +
                " and t_project_instance.complete_date < current_date" +
                " order by" +
                " t_project_instance.complete_date; ";
            DataTable dt = SQLpostgres.GetDataTable(
                sql
            );

            return dt;
        }

        public static DataTable get每日半成品检查()
        {
            String sql = "-- 2.每日半成品检查：\n" +
                " select\n" +
                "  t_barcode_wip_instance.barcode as barcode,--成衣条码\n" +
                "  t_order_report_p.stylenamecn as stylenamecn,--款式名称\n" +
                "  t_order_report_p.materialcode as materialcode,--面料号\n" +
                 " SPLIT_PART(t_order_report_p.stylesizecd, '-', 2) as size,--尺码\n" +
                 " t_barcode_wip_instance.stepname as stepname,--工序名称\n" +
                 " t_barcode_wip_instance.actualfinishdate as actualfinishdate--完成时间\n" +
                " from" +
                "  t_barcode_wip_instance\n" +
                " left join t_order_report_p on\n" +
                 " t_barcode_wip_instance.orderid = t_order_report_p.orderid\n" +
                " where" +
                 " t_barcode_wip_instance.stepname = '半成品检查'\n" +
                 " and t_barcode_wip_instance.\"version\" = '0'\n" +
                 " and t_barcode_wip_instance.actualfinishdate >= current_date - 1\n" +
                 " and t_barcode_wip_instance.actualfinishdate < current_date\n" +
                " order by\n" +
                 " t_barcode_wip_instance.actualfinishdate; ";
            DataTable dt = SQLpostgres.GetDataTable(
                sql
            );

            return dt;
        }

        public static DataTable get每日成品检查()
        {
            String sql = "-- 3.每日成品检查：\n" +
                " select\n" +
                 " t_barcode_wip_instance.barcode as barcode,--成衣条码\n" +
                 " t_order_report_p.stylenamecn as stylenamecn,--款式名称\n" +
                 " t_order_report_p.materialcode as materialcode,--面料号\n" +
                 " SPLIT_PART(t_order_report_p.stylesizecd, '-', 2) as size,--尺码\n" +
                 " t_barcode_wip_instance.stepname as stepname,--工序名称\n" +
                 " t_barcode_wip_instance.actualfinishdate as actualfinishdate--完成时间\n" +
                " from" +
                 " t_barcode_wip_instance\n" +
                " left join t_order_report_p on\n" +
                 " t_barcode_wip_instance.orderid = t_order_report_p.orderid\n" +
                " where" +
                 " t_barcode_wip_instance.stepname = '成品检查'\n" +
                 " and t_barcode_wip_instance.\"version\" = '0'\n" +
                 " and t_barcode_wip_instance.actualfinishdate >= current_date - 1\n" +
                 " and t_barcode_wip_instance.actualfinishdate < current_date\n" +
                " order by\n" +
                 " t_barcode_wip_instance.actualfinishdate; ";
            DataTable dt = SQLpostgres.GetDataTable(
                sql
            );

            return dt;

        }

        public static DataTable get每日钉锁()
        {
            String sql = " -- 4.每日钉锁：\n" +
                " select\n" +
                " t_barcode_wip_instance.barcode as barcode,--成衣条码\n" +
                " t_order_report_p.stylenamecn as stylenamecn,--款式名称\n" +
                 " t_order_report_p.materialcode as materialcode, --面料号\n" +
                 " SPLIT_PART(t_order_report_p.stylesizecd, '-', 2) as size,--尺码\n" +
                 " t_barcode_wip_instance.stepname as stepname,--工序名称\n" +
                 " t_barcode_wip_instance.actualfinishdate as actualfinishdate--完成时间\n" +
                " from" +
                 " t_barcode_wip_instance\n" +
               " left join t_order_report_p on\n" +
                 " t_barcode_wip_instance.orderid = t_order_report_p.orderid\n" +
                " where" +
                 " t_barcode_wip_instance.stepname = '钉锁'\n" +
                 " and t_barcode_wip_instance.\"version\" = '0'\n" +
                 " and t_barcode_wip_instance.actualfinishdate >= current_date - 1\n" +
                 " and t_barcode_wip_instance.actualfinishdate < current_date\n" +
                " order by\n" +
                 " t_barcode_wip_instance.actualfinishdate; ";
            DataTable dt = SQLpostgres.GetDataTable(
                sql
            );

            return dt;
        }
        public static DataTable get每日入库()
        {
            String sql = " -- 5.每日入库：\n" +
                    " select\n" +
                    " t_stock_inout_details.barcode as barcode,--成衣条码\n" +
                    " t_stock_inout_details.batchno as batchno,--生产批号\n" +
                    " t_stock_inout_details.stylenamecn as stylenamecn,--款式名称\n" +
                    " t_stock_inout_details.fabriccode as fabriccode,--面料号\n" +
                    " SPLIT_PART(t_stock_inout_details.stylesizecd, '-', 2) as size,--尺码\n" +
                    " t_stock_inout_details.salestime as salestime--完成时间\n" +
                    " from" +
                     " t_stock_inout_details\n" +
                    " where" +
                     " t_stock_inout_details.inoutflag = '1'\n" +
                     " and t_stock_inout_details.storeid = '14'\n" +
                     " and t_stock_inout_details.salestime >= current_date - 1\n" +
                     " and t_stock_inout_details.salestime < current_date\n" +
                    " order by\n" +
                    " t_stock_inout_details.salestime desc;";
            DataTable dt = SQLpostgres.GetDataTable(
                sql
            );

            return dt;
        }

        /// <summary>
        /// 门店往工厂调拨的调拨
        /// </summary>
        /// <returns></returns>
        public static DataTable get门店调拨工厂列表()
        {
            String sql = "select\n" +
                        "t_stock_inout_title.inoutcode ,\n" +
                        "t_stock_inout_title.inouttype ,\n" +
                        "t_stock_inout_title.outstorename ,\n" +
                        "t_stock_inout_title.inoutdate ,\n" +
                        "t_stock_inout_title.name,\n" +
                        "t_stock_inout_details.barcode ,\n" +
                        "t_stock_inout_details.stylenamecn ,\n" +
                        "t_stock_inout_details.stylesizecd ,\n" +
                        "t_stock_inout_details.fabriccode ,\n" +
                        "t_stock_inout_details.fabricname\n" +
                        "from\n" +
                        "t_stock_inout_title\n" +
                        "left join t_stock_inout_details\n" +
                        "on t_stock_inout_details.titleid  = t_stock_inout_title.id\n" +
                        "where\n" +
                        "t_stock_inout_title.outstoreid in ('1', '2')\n" +
                        "and t_stock_inout_title.inoutflag = '0'\n" +
                        "and t_stock_inout_title.instoreid in ('6', '8', '5', '14')\n" +
                        "and t_stock_inout_title.inoutdate::timestamp  >= current_date -30\n" +
                        "order by\n" +
                        "t_stock_inout_title.inoutdate desc;\n";
            DataTable dt = SQLpostgres.GetDataTable(
                sql
            );
            return dt;
        }

        /// <summary>
        /// 实时库存
        /// </summary>
        /// <returns></returns>
        public static DataTable get实时库存()
        {
            String sql = "select\n" +
                        "v_stock_inout_details.barcode ,\n" +
                        "v_stock_inout_details.stylenamecn ,\n" +
                        "v_stock_inout_details.stylesizecd ,\n" +
                        "v_stock_inout_details.fabriccode ,\n" +
                        "v_stock_inout_details.fabricname ,\n" +
                        "v_stock_inout_details.fabriccomposition ,\n" +
                        "v_stock_inout_details.salestime ,\n" +
                        "v_stock_inout_details.inouttype ,\n" +
                        "v_stock_inout_details.storeid ,\n" +
                        "v_stock_inout_details.storename ,\n" +
                        "t_wip_report_p.orderid ,\n" +
                        "t_wip_report_p.orderno ,\n" +
                        "t_wip_report_p.orderdate ,\n" +
                        "t_wip_report_p.urgentflg\n" +
                        "from\n" +
                        "v_stock_inout_details\n" +
                        "left join t_wip_report_p on v_stock_inout_details.batchno =t_wip_report_p.batchno\n" +
                        "where\n" +
                        "v_stock_inout_details.inoutflag = '1'\n" +
                        "and v_stock_inout_details.storeid in ('1', '2', '5', '14', '8');\n";
            DataTable dt = SQLpostgres.GetDataTable(
                sql
            );
            return dt;
        }
    }
}
